#==============================================================================#
#####        ESTADÍSTICAS DE TRANSPORTE (SINIESTROS DE TRÁNSITO)           #####
#==============================================================================#
#==============================================================================#
##    GENERACIÓN DE TABULADOS - ESTADÍSTICAS DE TRANSPORTE TRIMESTRAL      ####*
#==============================================================================#
#==============================================================================#
#####           INSTITUTO NACIONAL DE ESTADÍSTICA Y CENSOS (INEC)          #####*
#==============================================================================#
# FUENTE: Estadísticas de Transporte
# RESPONSABLE: Gestión de Estadísticas Estructurales (GESE).
# Fecha de elaboración: Agosto 2023.
# Fecha de Última actualización: 04/05/2026.
# ==============================================================================*
# ==============================================================================*
# Elaborado por:
# Unidad de Gestión de Estadísticas Estructurales (GESE).
# Gabriela Hidalgo
# ==============================================================================*
# Roberto Chaves
# Jefe de Gestión de Estadísticas Estructurales (GESE)
# Instituto Nacional de Estadística y Censos
# ==============================================================================*
# Aprobado por: 
# Diana Barco
# Directora de Estadísticas Económicas (DECON)
# Instituto Nacional de Estadística y Censos 
# Diana_Barco@inec.gob.ec
# ==============================================================================*

# ==============================================================================*

######                           INSTRUCCIONES                             #####*

# 1.- Descargar la base de datos de las Estadísticas de Siniestros de Tránsito, 
#     (I trimestre) en formato SPSS de la página web del INEC.
#    (https://www.ecuadorencifras.gob.ec/siniestros-transito-trimestral/).

# 2.- En el disco C: de su computadora, crear una carpeta llamada "Tabulados ESTRA". 
# En esta carpeta  guardar las bases de datos (históricas) y el archivo: 
# “2026_TABULADOS_TRIMESTRAL_DISEÑO", aquí se exportará todos los resultados 
# en Excel (Tabulados).
# ==============================================================================*

# **************************************************************************** #
######                                NOTA                                 ##### *
#
# En este SCRIPT, se encuentra toda la programación necesaria para la generación
# de los diferentes tabulados. Estos se ha realizado en R-STUDIO. 

# **************************************************************************** #

# ============================================================================ #
######                            PASOS PREVIOS                           ######
# ============================================================================ #

##### Limpiar el espacio de trabajo #####

rm(list=ls())
cat("\014")

##### Instalacion de paquetes #####

"Cargar librerias y en caso de no existir, las instale"

estra_lib <- function(libreria) {
  if (!requireNamespace(as.character(libreria), quietly = TRUE)) {
    install.packages(as.character(libreria))
  }
  library(libreria, character.only = TRUE)
}

#==============================================================================#
# CARGAR LIBRERIAS                                                             #
#==============================================================================#

estra_lib("tidyverse")
estra_lib("openxlsx")
estra_lib("dplyr")
estra_lib("haven")
estra_lib("stringr")
estra_lib("janitor")
estra_lib("Hmisc")
estra_lib("foreign")
estra_lib("sjlabelled")
estra_lib("stringi")

#
#==============================================================================#
# Importar base de datos  y ruta de trabajo
#==============================================================================#

setwd("C:Tabulados") # Directorio de trabajo 
dir()  # Ver los documentos

#
#==============================================================================#
# Carga de bases de datos
#==============================================================================#

siniestros.I <- read.spss("2026_SINIESTROS_TRÁNSITO_BDD_IT.sav",to.data.frame=TRUE)

#==============================================================================#
#####                        CREACION DE FUNCIONES                         ##### 
#==============================================================================#

"Funcion para mover cualquier fila al inicio"
final <- function(df, row) {
  stopifnot(nrow(df) >= row) 
  rbind(df[row, ], df[-row, ])
}

"Funcion para realizar los tabulados con total de colunma al inicio"
estra <- function(df, vertical, horizontal) { 
  tab <- df %>% 
    select(all_of(c(vertical, horizontal))) %>%  
    mutate_if(is.double, as_factor) %>% 
    group_by(!!!syms(vertical),!!!syms(horizontal))%>%
    tally() %>%
    spread(horizontal, `n`)%>% 
    mutate(Total = rowSums(across(where(is.numeric)), na.rm=TRUE)) %>% 
    select(vertical,Total, everything()) %>% 
    ungroup() %>% 
    bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
    rename_at(-c(1:2), ~ str_to_title(.)) %>% 
    mutate_at(c(1:1), ~ str_to_title(.)) %>% 
    mutate_at(c(2:ncol(.)),~replace(.,is.na(.),0))
  
  return(tab)
}


"Funcion para tabulados con dos variables y total al final"
estra.2 <- function(df, vertical, horizontal, relleno) { 
  tab <- df %>% 
    select(all_of(c(vertical, horizontal, relleno))) %>%  
    group_by(!!!syms(vertical),!!!syms(horizontal))%>%
    summarise(Total = (sum(!!!syms(relleno),na.rm = T))) %>%
    spread(horizontal, Total) %>% 
    mutate(Total = rowSums(across(where(is.numeric)), na.rm=TRUE)) %>% 
    ungroup() %>% 
    bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
    rowwise() %>% 
    rename_at(-c(1:1), ~ str_to_title(.)) %>% 
    mutate_at(c(1:1), ~ str_to_title(.)) 
  
  tab <- tab %>% 
    mutate_at(c(1), ~replace(.,is.na(.) ,"Total Nacional")) %>% 
    arrange(across(1)) %>% 
    arrange(if_else(grepl("^otra", tolower(.[[1]])), 1, 0)) %>%
    arrange(desc(if_else(grepl("Total Nacional", .[[1]]), 1, 0))) %>% 
    mutate_if(is.numeric,~ifelse(is.na(.),0,.)) %>% 
    select(1, Total, everything())
  
  return(tab)
}


# ============================================================================ #
######     CREACION DE TABULADOS PARA siniestros.I DE TRANSITO         ######
# ============================================================================ #
"En esta sección se generarán los tabulados concernientes a siniestros de transito (S.T.)"

##### Tabulado 1: S.T. segun mes, Enero - Diciembre por provincia #####

st1.1 <- estra(siniestros.I, vertical = "PROVINCIA", horizontal = "MES") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  .[-21,] %>% 
  arrange(PROVINCIA) %>% 
  slice(1,2,4,3, 5:25) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total"))
st1.1$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st1.1$PROVINCIA)
st1 <- final(st1.1,nrow(st1.1))
rm(st1.1)

##### Tabulado 2: S.T. segun clase, Enero - Diciembre por provincia #####

st2.1 <- estra(siniestros.I,vertical = "PROVINCIA", horizontal = "CLASE") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  .[-21,] %>% 
  arrange(PROVINCIA) %>% 
  slice(1,2,4,3, 5:25)%>% 
  select(PROVINCIA, Total,`Choques`,`Pérdida De Pista`, `Atropellos`,
         `Estrellamientos`, `Rozamientos`,`Caída De Pasajeros`, `Volcamientos`,
         `Otras Clases`) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total"))
st2.1$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st2.1$PROVINCIA)
st2 <- final(st2.1,nrow(st2.1))
rm(st2.1)

##### Tabulado 3: S.T. segun meses, Enero - Diciembre por clase #####

st3 <- estra(siniestros.I,vertical = "CLASE", horizontal = "MES") %>% 
  mutate(aux = ifelse(CLASE == "Otras Clases",1,0)) %>%
  arrange(CLASE) %>% 
  arrange(aux) %>% 
  select(-aux) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total")) %>% 
  arrange(CLASE == "Otras Clases", desc(Total))
st3$CLASE <-gsub("Caída De Pasajeros","Caída de pasajeros", st3$CLASE)
st3$CLASE <-gsub("Pérdida De Pista","Pérdida de pista", st3$CLASE)
st3$CLASE <-gsub("Otras Clases","Otras clases", st3$CLASE)

##### Tabulado 4: S.T. segun causa, Enero - Diciembre por provincia #####

st4.1 <- estra(siniestros.I,vertical = "PROVINCIA", horizontal = "CAUSA") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  .[-21,] %>%    
  arrange(PROVINCIA) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>%
  slice(1,2,4,3, 5:25)%>% 
  select(PROVINCIA, Total,`Impericia E Imprudencia Del Conductor`,`Exceso Velocidad`, `No Respeta Las Señales De Tránsito`,
         `Embriaguez O Droga`, `Mal Rebasamiento Invadir Carril`,`Factores Climáticos`, `Imprudencia Del Peatón`,
         `Mal Estado De La Vía`,`Daños Mecánicos`,`Otras Causas`) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total"))
st4.1$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st4.1$PROVINCIA)
st4 <- final(st4.1,nrow(st4.1))
rm(st4.1)


##### Tabulado 5: S.T. segun meses, Enero - Diciembre por causa #####

st5 <- estra(siniestros.I,vertical = "CAUSA", horizontal = "MES") %>% 
  mutate(aux = ifelse(CAUSA == "Otras causas",1,0)) %>%
  arrange(CAUSA) %>% 
  arrange(aux) %>% 
  select(-aux) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total")) %>% 
  arrange(CAUSA == "Otras Causas", desc(Total))
st5$CAUSA <-paste0(toupper(substr(tolower(st5$CAUSA), 1, 1)),
                   substr(tolower(st5$CAUSA),2,nchar(st5$CAUSA)))

##### Tabulado 6: Victimas en S.T., por provincia #####

st6.1 <- siniestros.I %>% 
  select(PROVINCIA, TOTAL_VICTIMAS,NUM_FALLECIDO,NUM_LESIONADO) %>% 
  mutate(PROVINCIA = as_character(PROVINCIA)) %>% 
  group_by(PROVINCIA) %>%
  summarise(VÍCTIMAS= sum(TOTAL_VICTIMAS, na.rm = TRUE),
            FALLECIDOS = sum(NUM_FALLECIDO, na.rm = TRUE),
            LESIONADOS = sum(NUM_LESIONADO, na.rm = TRUE)) %>% 
  ungroup() %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>%
  rename_at(-c(1:2), ~ str_to_title(.)) %>% 
  mutate_at(c(1:1), ~ str_to_title(.)) %>% 
  arrange(PROVINCIA) %>% 
  slice(1,2,4,3, 5:25)%>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total"))
st6.1$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st6.1$PROVINCIA)
st6 <- final(st6.1,nrow(st6.1))
rm(st6.1)


##### Tabulado 7: Victimas en S.T., por GAD municipal #####

st7.1 <- siniestros.I %>% 
  select(CANTÓN, TOTAL_VICTIMAS,NUM_FALLECIDO,NUM_LESIONADO) %>% 
  mutate(CANTÓN = as_character(CANTÓN)) %>% 
  group_by(CANTÓN) %>%
  summarise(VÍCTIMAS= sum(TOTAL_VICTIMAS, na.rm = TRUE),
            FALLECIDOS = sum(NUM_FALLECIDO, na.rm = TRUE),
            LESIONADOS = sum(NUM_LESIONADO, na.rm = TRUE)) %>% 
  ungroup() %>% 
  complete(CANTÓN,  fill = list(total = 0)) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>%
  rename_at(-c(1:2), ~ str_to_title(.)) %>% 
  mutate_at(c(1:1), ~ str_to_title(.)) %>% 
  bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE)))

st7.1 <- st7.1 %>% 
  mutate(CANTÓN=ifelse(rank(-VÍCTIMAS, ties.method = "first") <= 11, CANTÓN, 
                       "Resto de GAD")) %>% 
  group_by(CANTÓN) %>% 
  summarise(across(everything(), sum)) %>% 
  ungroup() %>% 
  arrange(CANTÓN == "Resto de GAD", desc(VÍCTIMAS)) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total"))
st7.1$CANTÓN <-gsub("San Jacinto De Yaguachi","San Jacinto de Yaguachi", st7.1$CANTÓN)
st7.1$CANTÓN <-gsub("Distrito Metropolitano De Quito","Distrito Metropolitano de Quito", st7.1$CANTÓN)
st7 <- final(st7.1,nrow(st7.1))
rm(st7.1)

##### Tabulado 8: Lesionados en S.T., por provincia #####

st8 <- estra.2(siniestros.I, vertical = "PROVINCIA", horizontal = "MES", relleno = "NUM_LESIONADO") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  .[-21,] %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total")) %>% 
  arrange(PROVINCIA != "Total Nacional", iconv(PROVINCIA, from = "UTF-8", to = "ASCII//TRANSLIT"))
st8$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st8$PROVINCIA)

##### Tabulado 9: Fallecidos en S.T., por provincia #####

st9<- estra.2(siniestros.I, vertical = "PROVINCIA", horizontal = "MES", relleno = "NUM_FALLECIDO") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  .[-21,] %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total")) %>% 
  arrange(PROVINCIA != "Total Nacional", iconv(PROVINCIA, from = "UTF-8", to = "ASCII//TRANSLIT"))
st9$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st9$PROVINCIA)

##### Tabulado 10: Indicadores en S.T.,  por provincia #####

st10 <- siniestros.I %>% 
  select(PROVINCIA, TOTAL_VICTIMAS,NUM_FALLECIDO, NUM_LESIONADO) %>% 
  mutate(PROVINCIA = as_character(PROVINCIA)) %>% 
  group_by(PROVINCIA) %>%
  summarise(SINIESTROST= n(),
            FALLECIDOS = sum(NUM_FALLECIDO, na.rm = T),
            LESIONADOS = sum(NUM_LESIONADO, na.rm = T)) %>%  
  ungroup() %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>% 
  bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
  mutate_if(is.numeric, ~replace(., is.na(.), 0)) %>%
  rename_at(-c(1:2), ~ str_to_title(.)) %>% 
  mutate_at(c(1:1), ~ str_to_title(.)) %>% 
  arrange(PROVINCIA) %>% 
  slice(1,2,4,3, 5:25)%>% 
  mutate(Tasafallecidos =(Fallecidos/SINIESTROST)*100)%>%
  mutate(Tasalesionados =(Lesionados/SINIESTROST)*100) %>%
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total Nacional")) 
  st10 <- final(st10,nrow(st10))
st10$PROVINCIA <-gsub("Santo Domingo De Los Tsáchilas","Santo Domingo de los Tsáchilas", st10$PROVINCIA)


##### Tabulado 11: Siniestros histórico #####

siniestros.I <- siniestros.I %>% 
  mutate(t=1)

st11.1 <- estra(siniestros.I, vertical = "PROVINCIA", horizontal = "t") %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>%
  .[-21,] %>%
  arrange(PROVINCIA) %>%
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>%
  slice(1,2,4,3, 5:25) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total")) %>% 
  select(-3)
st11.1 <- final(st11.1,nrow(st11.1))

st11.2 <- siniestros.I %>% 
  select(PROVINCIA, NUM_FALLECIDO) %>% 
  mutate(PROVINCIA = as_factor(PROVINCIA)) %>% 
  group_by(PROVINCIA) %>%
  summarise("Fallecidos" = sum(NUM_FALLECIDO, na.rm = TRUE)) %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>%
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>%
  slice(1,2,4,3, 5:25)%>% 
  arrange(as.character(PROVINCIA)) %>% 
  bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
  rename_at(-c(1:1), ~ str_to_title(.)) %>% 
  mutate_at(c(1:1), ~ str_to_title(.)) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total de Fallecidos")) %>% 
  arrange(stri_sort(PROVINCIA, locale = "es", numeric = TRUE))
st11.2 <- final(st11.2,nrow(st11.2))

st11.3 <- siniestros.I %>% 
  select(PROVINCIA, NUM_LESIONADO) %>% 
  mutate(PROVINCIA = as_factor(PROVINCIA)) %>% 
  group_by(PROVINCIA) %>%
  summarise("Lesionados" = sum(NUM_LESIONADO, na.rm = TRUE)) %>% 
  complete(PROVINCIA=levels(siniestros.I$PROVINCIA),  fill = list(total = 0)) %>%
  mutate_if(is.numeric, ~replace(., is.na(.), 0))%>%
  slice(1,2,4,3, 5:25)%>% 
  arrange(as.character(PROVINCIA)) %>% 
  bind_rows(summarise(., across(where(is.numeric), sum, na.rm=TRUE))) %>% 
  rename_at(-c(1:1), ~ str_to_title(.)) %>% 
  mutate_at(c(1:1), ~ str_to_title(.)) %>% 
  mutate_at(c(1),~replace(.,is.na(.),"Total de Lesionados")) %>% 
  arrange(stri_sort(PROVINCIA, locale = "es", numeric = TRUE)) 
st11.3 <- final(st11.3,nrow(st11.3))

st11 <- bind_rows(st11.1,st11.2,st11.3) %>% 
  mutate(total=coalesce(Total,`Fallecidos`,`Lesionados`)) %>% 
  select(ncol(.))
rm(st11.1, st11.2, st11.3)

# ============================================================================ #
#####               DISEÑO Y EXPORTACIÓN DE TABULADOS A EXCEL              ##### 
# ============================================================================ #
"En esta sección se diseñara y exportara los tabulados en formato excel"

##### Configuracion tabulados #####
wb <- loadWorkbook("2026_TABULADOS_TRIMESTRAL_DISEÑO.xlsx")

#Tabulado 1
writeData(wb,
          "1",
          st1, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "1", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 2
writeData(wb,
          "2",
          st2, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "2", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 3
writeData(wb,
          "3",
          st3, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "3", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 4
writeData(wb,
          "4",
          st4, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "4", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 5
writeData(wb,
          "5",
          st5, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "5", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 6
writeData(wb,
          "6",
          st6,
          startRow = 3,
          startCol = 1,
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb,
          "6",
          "Total Nacional",
          startCol = 1,
          startRow = 3,
          rowNames = FALSE)

#Tabulado 7

writeData(wb,
          "7",
          st7,
          startRow = 3,
          startCol = 1,
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb,
          "7",
          "Total Nacional",
          startCol = 1,
          startRow = 3,
          rowNames = FALSE)


#Tabulado 8

writeData(wb,
          "8",
          st8, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "8", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 9

writeData(wb,
          "9",
          st9, 
          startRow = 4, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "9", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 4, 
          rowNames = FALSE)

#Tabulado 10
writeData(wb,
          "10",
          st10, 
          startRow = 3, 
          startCol = 1,  
          colNames = FALSE,
          withFilter = FALSE)

writeData(wb, 
          "10", 
          "Total Nacional", 
          startCol = 1, 
          startRow = 3, 
          rowNames = FALSE)

#Tabulado 11

writeData(wb,
          "11",
          st11,
          startRow = 4,
          startCol = 11,
          colNames = FALSE,
          withFilter = FALSE)


#####################   Exportación de tabulados a Excel   #####################

saveWorkbook(wb, "IT_2026_TABULADOS_SINIESTROS.xlsx",overwrite = TRUE)

#####                           FIN DEL SCRIPT                             #####

